﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using RFIDStockManagement.IDAL;
using RFIDStockManagement.Common.Public;
using System.Data.Common;
using System.Data;

namespace RFIDStockManagement.DAL
{
    public class OutStockCheck:IOutStockCheck
    {
        private DbHelper _dbHelper;
        private const string SELECT_UNOUTRFID = @"SELECT t2.FName,t1.FRFID From rfid_Item_Detail t1 with(nolock) 
Inner Join rfid_Item t2 on t1.FItemID = t2.FID 
Inner Join {0} t3 on t1.FRFID = t3.FRFID
Where t1.FStatus <=1";

        private const string UPDATE_ITEM_RFID_STATUES = @"UPdate rfid_Item_Detail Set FStatus = 2 
 Where Exists(select 1 From {0} tt1 where rfid_Item_Detail.FRFID = tt1.FRFID ) And rfid_Item_Detail.FStatus >1";

        private const string UNOUTSTOCKITEMINFO = "物料:{0};RFID:{1}";

        private const string CLEARTMPTABLE_SQL = @"IF object_id('tempdb..{0}') is not null
Begin
	drop table {0}
end ";


        public OutStockCheck()
        {
            _dbHelper = new DbHelper();
        }

        #region IOutStockCheck 成员

        public List<string> CheckOutStockRFIDs(List<string> rfids)
        {
            StringBuilder sql ;
            DataTable unOutStockInfos;
            Trans trans =new Trans();
            
            int len = rfids.Count;
            string tmpTableName = "#TmpVCheck_" + Guid.NewGuid().ToString();
            tmpTableName = tmpTableName.Replace("-", "_");

            List<string> newRetVal = new List<string>();
            
            if (len == 0) return newRetVal;

            sql = new StringBuilder();
            sql.AppendLine("Create Table "+ tmpTableName+"(FRFID nvarchar(200) )" );

            for (int i = 0; i < len; i++)
            {
                if (i % 50 == 0)
                {
                    sql.AppendLine("");
                    sql.Append("Insert Into ");
                    sql.Append(tmpTableName);
                    sql.AppendLine("(FRFID)");
                    sql.Append("Select ");
                    sql.AppendLine(PublicFunction.AddToString(rfids[i]));
                }
                else
                {
                    sql.Append("Union All Select ");
                    sql.AppendLine(PublicFunction.AddToString(rfids[i]));
                }
            }
            //数据都插入到了临时表里面

            DbCommand cmd = _dbHelper.GetSqlStringCommand(sql.ToString());
            _dbHelper.ExecuteNonQuery(cmd, trans);

            try
            {
                cmd = _dbHelper.GetSqlStringCommand(string.Format(UPDATE_ITEM_RFID_STATUES, tmpTableName));
                _dbHelper.ExecuteNonQuery(cmd,trans);
                cmd = _dbHelper.GetSqlStringCommand(string.Format(SELECT_UNOUTRFID, tmpTableName));

                unOutStockInfos = _dbHelper.ExecuteDataTable(cmd,trans);

                trans.Commit();
                foreach (DataRow x in unOutStockInfos.Rows)
                {
                    newRetVal.Add(string.Format(UNOUTSTOCKITEMINFO, x["FName"].ToString(), x["FRFID"].ToString()));
                }

            }
            catch (Exception ex)
            {
                throw;
            }
            finally //这里一定要吧数据库的临时表去除掉
            {
                cmd = _dbHelper.GetSqlStringCommand(string.Format(CLEARTMPTABLE_SQL,tmpTableName));
                _dbHelper.ExecuteNonQuery(cmd);
            }

            return newRetVal;
        }

        #endregion
    }
}
